package com.guodi.map.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.guodi.common.utils.HttpRequester;
import com.guodi.common.utils.HttpRespons;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.postgresql.util.PSQLException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @描述：sde数据库操作工具类
 * @作者: 陈宏成
 * @日期: 2021-09-24
 */
@Slf4j
@Component
public class SDEdbUtils {

    // SDE空间数据库连接信息
    private static String driverClassName;
    private static String jdbcUrl;
    private static String userName;
    private static String password;
    // 数据源连接池
    private static DataSource datasource;

    /**
     * 获取sde指定表指定字段的取值列表
     * @param dataSourceId    数据源ID
     * @param tableName       空间表名称
     * @param fieldName       字段名称
     * @param soeUrl          soe地址
     * @param returnNumMax    返回最大记录数
     * @author chenh
     * @date 2022-04-22 11:03
     * @since 1.0
     * @return java.util.List
     */
    public static List listFieldValue2(String dataSourceId,String tableName,String fieldName,String soeUrl,Integer returnNumMax){
        HttpRequester requester =new HttpRequester();
        Map<String,String> params = new HashMap<>();
        List resultList = new ArrayList();
        try {
            params.put("id",dataSourceId);
            params.put("f","pjson");
            params.put("tableName",tableName);
            params.put("fieldName",fieldName);
            params.put("maxCount",returnNumMax+"");
            log.info("params:{}",params);
            HttpRespons respons = requester.sendPost2(soeUrl, params);
            String result = respons.getContent();
            JSONObject valueResult = (JSONObject) JSONObject.parse(result);
            Object data = valueResult.get("data");
            if(data!=null){
                JSONObject dataJson = (JSONObject) data;
                Object values = dataJson.get("values");
                if(values!=null){
                    JSONArray valueArray = (JSONArray) values;
                    for (int i=0;i<valueArray.size();i++){
                        resultList.add(valueArray.get(i));
                    }
                }
            }
        } catch (IOException e) {
            log.info("fieldValueSOEUrl error:{}",e.getMessage());
            log.info(e.getMessage(),e);
        }
        return resultList;
    }

    /**
     * @描述：获取sde指定表指定字段的取值列表
     * @参数：table 表名
     * @参数：field 字段名
     * @参数：returnNumMax 返回数量上限
     * @作者: 陈宏成
     * @日期: 2021-09-24
     */
    public synchronized List<Object> listFieldValue(String table, String field, int returnNumMax) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        // 处理参数
        checkParams(table, field);
        // 查询数据库
        try{
            // 获取连接
            conn = getConnection();
            String sql = "SELECT DISTINCT " + field + " FROM  " + table + " order by " + field + " asc";
            // 加个分页限制
            sql = addPageSql(sql, 1 , returnNumMax);
            statement = conn.createStatement();
            // 获取结果
            resultSet = statement.executeQuery(sql);
            List<Object> fieldValueList = getColumnValueForList(1, resultSet);
            return fieldValueList;
        }catch (SQLSyntaxErrorException sqlSyntaxErrorException) {
            // oracle的
            String message = sqlSyntaxErrorException.getMessage();
            if (StringUtils.isNotBlank(message) && message.contains("ORA-00904")){
                message = "字段（" + field + "）不存在";
            }else{
                message = "字段（" + field + "）查询失败";
            }
            RuntimeException runtimeException = new RuntimeException(message);
            runtimeException.addSuppressed(sqlSyntaxErrorException);
            throw runtimeException;
        }catch (PSQLException psqlException){
            // postgresql
            String message = "字段（" + field + "）查询失败";
            RuntimeException runtimeException = new RuntimeException(message);
            runtimeException.addSuppressed(psqlException);
            throw runtimeException;
        }finally {
            close(conn, statement, resultSet);
        }
    }

    /***
     * 检查SQL非法参数，非法参数包括  ,:;()[]\@'" =
     * @作者: 陈宏成
     * @日期: 2021-09-24
     * */
    private  void checkParams(String ... params){
        Pattern pattern = Pattern.compile("[,:;\\(\\)\\[\\]\\@'\" =]");
        for(String param : params) {
            Matcher matcher = pattern.matcher(param);
            boolean b = matcher.find();
            if (b){
                throw new RuntimeException("参数存在非法字符");
            }
        }
    }


    /***
     * 获取某一列的所有值
     * @参数： columnIndex 列下标，从1开始
     * @参数： resultSet 查询结果集
     * @作者: 陈宏成
     * @日期: 2021-09-24
     * */
    private  List<Object> getColumnValueForList(int columnIndex, ResultSet resultSet) throws SQLException {
        List<Object> fieldValueList = new LinkedList<>();
        while (resultSet.next()){
            Object object = resultSet.getObject(1);
            fieldValueList.add(object);
        }
        return fieldValueList;
    }


    /***
     * 获取连接
     * @作者: 陈宏成
     * @日期: 2021-09-24
     * */
    private  Connection getConnection() throws ClassNotFoundException, SQLException {
        // 初始化
        if (datasource == null){
            init();
        }
        Connection connection = datasource.getConnection();
        return connection;
    }

    /***
     * 释放资源
     * @作者: 陈宏成
     * @日期: 2021-09-24
     * */
    private  void close(Connection conn,Statement statement,ResultSet resultSet )throws SQLException  {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        }finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            }finally {
                try {
                    if (conn != null) {
                        // 并不是真正释放，只是还给线程池
                        conn.close();
                    }
                }finally {

                }
            }
        }
    }

    /***
     * 分页拼装SQL
     * 入参： sql 原始sql
     * 入参： fromPage 页码，从1开始
     * 入参： pageSize 页面大小
     * 出参： 拼接了分页的SQL
     * @作者: 陈宏成
     * @日期: 2021-09-26
     * */
    private  String addPageSql(String sql, int fromPage, int pageSize){
        String newSql = null;
        String tempTableName = "temp" + System.currentTimeMillis();
        if (jdbcUrl.toLowerCase().contains("oracle")){
            String tempTableName2 = tempTableName + "2";
            int offset = (fromPage - 1) * pageSize + 1;
            int offsetEnd = offset + pageSize - 1;
            newSql = "SELECT " + tempTableName2 + ".*  FROM ( SELECT " + tempTableName + ".*, ROWNUM as ROWNUM2 FROM " +
                    "( " + sql +" ) " + tempTableName +
                    " WHERE ROWNUM <= " + offsetEnd +" )" + tempTableName2 + " WHERE ROWNUM2 >= " + offset ;
        }else if (jdbcUrl.toLowerCase().contains("postgresql")){
            int offset = (fromPage - 1) * pageSize;
            newSql = "SELECT " + tempTableName + ".* from ( " + sql + " ) " + tempTableName + " LIMIT " + pageSize + " OFFSET  " + offset;
        }else if(jdbcUrl.toLowerCase().contains("mysql")){
            int offset = (fromPage - 1) * pageSize;
            newSql = "SELECT " + tempTableName + ".* from ( " + sql + " ) " + tempTableName + " LIMIT " + offset + " ,  " + pageSize;
        }else {
            throw new RuntimeException("SDEdbUtils的String addPageSql(String sql, int fromPage, int pageSize) 方法目前只支持 oracle、postgresql、mysql ");
        }
        return newSql;
    }



    @Value("${spring.datasource.db2.driver-class-name}")
    public void setDriverClassName(String driverClassName) {
        SDEdbUtils.driverClassName = driverClassName;
    }

    @Value("${spring.datasource.db2.jdbc-url}")
    public void setJdbcUrl(String jdbcUrl) {
        SDEdbUtils.jdbcUrl = jdbcUrl;
    }

    @Value("${spring.datasource.db2.username}")
    public void setUserName(String userName) {
        SDEdbUtils.userName = userName;
    }

    @Value("${spring.datasource.db2.password}")
    public void setPassword(String password) {
        SDEdbUtils.password = password;
    }

    private synchronized void init(){
        if (datasource == null){
            if (StringUtils.isBlank(driverClassName) || StringUtils.isBlank(jdbcUrl) || StringUtils.isBlank(userName) || StringUtils.isBlank(password) ){
                throw new RuntimeException("spring.datasource.db2配置错误");
            }
            HikariConfig config = new HikariConfig();
            config.setDriverClassName(driverClassName);
            config.setJdbcUrl(jdbcUrl);
            config.setUsername(userName);
            config.setPassword(password);
            datasource = new HikariDataSource(config);
        }
    }

}
